package database;

import com.mysql.jdbc.PreparedStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Jack
 */
public class Database {
    // JDBC driver name and database URL

    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://stevie.heliohost.org/jacktrb_telephone_company";
    //  Database credentials
    static final String USER = "jacktrb";
    static final String PASS = "11jack11";
    // jdbc Connection
    private static Connection conn = null;
    private static Statement stmt = null;

    //connect to database
    public static void connect() {
        try {
            Class.forName(JDBC_DRIVER);
            conn = (Connection) DriverManager.getConnection(DB_URL, USER, PASS);
        } catch (Exception except) {
            except.printStackTrace();
        }
    }

    public static int addUser(String name, String adress, String phone) {
        try {
            PreparedStatement  st=(PreparedStatement) conn.prepareStatement(
                    "INSERT INTO users (name,adress,phone) VALUES ('"
                    +name+"','"+adress+"','"+phone
                    +"')", Statement.RETURN_GENERATED_KEYS);
            if(st.executeUpdate()==0)
                throw new SQLException("Error with insert");
            
            ResultSet rs=st.getGeneratedKeys();
            if(rs.first()){
                return rs.getInt(1);
            }else
                return 0;
        } catch (SQLException sqlExcept) {
            sqlExcept.printStackTrace();
            return 0;
        } 
    }

    public static void removeUser(int userId) {
        try {
            stmt = (Statement) conn.createStatement();
            stmt.execute("DELETE FROM users WHERE id=" + userId);
            stmt.close();
        } catch (SQLException sqlExcept) {
            sqlExcept.printStackTrace();
        }
    }

    public static ArrayList<User> getUsersByName(String name) {
        String sql;
        //create query for getting user by name
        sql = "SELECT id, name, adress, phone,email,web,username,password FROM users WHERE name LIKE '%" + name + "%'";

        //query database for sql
        ArrayList<User> usersByName = queryDatabase(sql);
        return usersByName;
    }

    public static ArrayList<User> getUsersByPhone(String phone) {
        String sql;
        //create query for getting user by phone
        sql = "SELECT id, name, adress, phone,email,web,username,password FROM users WHERE phone LIKE '%" + phone + "%'";

        //query database for sql
        ArrayList<User> usersByPhone = queryDatabase(sql);
        return usersByPhone;
    }

    public static ArrayList<User> getUsersByAdress(String adress) {
        String sql;
        //create query for getting user by user
        sql = "SELECT id, name, adress, phone,email,web,username,password FROM users WHERE adress LIKE '%" + adress + "%'";

        //query database for sql
        ArrayList<User> usersByPhone = queryDatabase(sql);
        return usersByPhone;
    }

    private static ArrayList<User> queryDatabase(String sql) {
        ArrayList<User> users = new ArrayList<User>();
        try {
            stmt = conn.createStatement();
            //getting results from database
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String adress = rs.getString("adress");
                String phone = rs.getString("phone");
                String email = rs.getString("email");
                String web = rs.getString("web");
                String username = rs.getString("username");
                String pass = rs.getString("password");
                User user = new User(id, name, adress, phone, email, web, username, pass);



                users.add(user);
            }
            rs.close();
            //get bills for user
            ArrayList<Bill> allBills = new ArrayList<Bill>();
            ResultSet rsBills = stmt.executeQuery("SELECT * FROM bills");
            while (rsBills.next()) {
                int idBill = rsBills.getInt("id");
                int userid = rsBills.getInt("userid");
                int month = rsBills.getInt("month");
                int price = rsBills.getInt("price");
                allBills.add(new Bill(idBill, userid, month, price));
            }
            rsBills.close();
            for(int i=0;i<users.size();i++){
                ArrayList<Bill> userBills = new ArrayList<Bill>();
                for(int j=0;j<allBills.size();j++){
                    if(allBills.get(j).getUserId()==users.get(i).getId()){
                        userBills.add(allBills.get(j));
                    }
                }
                users.get(i).setBills(userBills);
            }

        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        }
        return users;
    }

    public static User login(String username, String password) {
        User user = null;

        //sql query for getting user data by username and password
        String sql = "SELECT * FROM users WHERE username='" + username + "' AND password='" + password + "'";

        //get user data from database
        try {
            stmt = conn.createStatement();
            //getting results from database
            ResultSet rs = stmt.executeQuery(sql);
            rs.first();
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String adress = rs.getString("adress");
            String phone = rs.getString("phone");
            String email = rs.getString("email");
            String web = rs.getString("web");
            rs.close();

            user = new User(id, name, adress, phone, email, web, username, password);

            //get bills for user
            ArrayList<Bill> bills = new ArrayList<Bill>();
            ResultSet rsBills = stmt.executeQuery("SELECT * FROM bills WHERE userid=" + id);
            while (rsBills.next()) {
                int idBill = rsBills.getInt("id");
                int userid = rsBills.getInt("userid");
                int month = rsBills.getInt("month");
                int price = rsBills.getInt("price");
                bills.add(new Bill(idBill, userid, month, price));
            }
            rsBills.close();

            user.setBills(bills);

        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            return user;
        }
    }

    public static void updateEmailAndWebAddress(String email, String webAddress, int userId) {
        try {
            stmt = (Statement) conn.createStatement();
            stmt.execute("UPDATE users SET email='" + email + "', web='" + webAddress + "' WHERE id=" + userId);
            stmt.close();
        } catch (SQLException sqlExcept) {
            sqlExcept.printStackTrace();
        }
    }

    public static void updateUser(int id, String name, String address, String phone, String email, String web, String username, String password) {
        String sql = "UPDATE users set name='" + name
                + "', adress='" + address
                + "', phone='" + phone
                + "', email='" + email
                + "', web='" + web
                + "', username='" + username
                + "', password='" + password
                + "' WHERE id=" + id;
        try {
            stmt = (Statement) conn.createStatement();
            stmt.execute(sql);
            stmt.close();
        } catch (SQLException sqlExcept) {
            sqlExcept.printStackTrace();
        }
    }
    //registring user, int code is user id in database

    public static void register(String username, String password, String code) {
        //sql for inserting username and password in database
        String sql = "UPDATE users SET username='" + username + "', password='" + password + "' WHERE id=" + code;

        //executing query
        try {
            stmt = conn.createStatement();
            stmt.execute(sql);
        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        }
    }

    public static int addBill(int userid,String month,String price){
        try {
            PreparedStatement  st=(PreparedStatement) conn.prepareStatement("INSERT INTO bills (userid,month,price) VALUES ("
                    +userid+","+month+","+price+")", Statement.RETURN_GENERATED_KEYS);
            if(st.executeUpdate()==0)
                throw new SQLException("Error with insert");
            
            ResultSet rs=st.getGeneratedKeys();
            if(rs.first()){
                return rs.getInt(1);
            }else
                return 0;
        } catch (SQLException sqlExcept) {
            sqlExcept.printStackTrace();
            return 0;
        } 
    }
    
    public static void removeBill(int billId){
        try {
            stmt = (Statement) conn.createStatement();
            stmt.execute("DELETE FROM bills WHERE id=" + billId);
            stmt.close();
        } catch (SQLException sqlExcept) {
            sqlExcept.printStackTrace();
        }
    }
    //shutdown database connection
    public static void shutdown() {
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException se2) {
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }

    }
}
